package database;

import interfaces.AbstractWish;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;

import wishes.WishToDrink;
import wishes.WishToEat;
import wishes.WishToSleep;
import wishes.WishToTakeMedicine;

import enums.WishType;

import model.CustomMap;
import model.Model;
import model.Stock;
import model.Wardrobe;
import model.WishFactory;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

public class DatabaseBot extends SQLiteOpenHelper
{
	private static DatabaseBot selfBot;
	
	Context context;
	
    //Datenbank-Version
    private static final int DATABASE_VERSION = 5;
    //Datenbank-Name
    private static final String DATABASE_NAME = "GraniDB";
    
    //Tabellen-Namen
    private static final String TABLE_GRANNI_NAME = "GraniDates";
    private static final String TABLE_SETTINGS_NAME = "Settings";
    private static final String TABLE_MODEL_NAME = "ModelSaves";
    private static final String TABLE_WISH_NAME = "WuenscheSaves";

    //Tabelen-Zeilen Namen
    private static final String KEY_GRANNIDATES_ID = "ID";
    private static final String KEY_GRANNIDATES_TIMESTAMP = "Timestamp";
    private static final String KEY_GRANNIDATES_TITLE = "Title";
    //------
    private static final String KEY_SETTINGS_ID = "ID";
    private static final String KEY_SETTINGS_TITLE = "Title";
    private static final String KEY_SETTINGS_VALUE = "Value";
    //------
    private static final String KEY_MODEL_ID = "ID";
    private static final String KEY_MODEL_TITLE = "Title";
    private static final String KEY_MODEL_VALUE = "Value";
    //------
    private static final String KEY_WISH_ID = "ID";
    private static final String KEY_WISH_TITLE = "Wunsch";
    private static final String KEY_WISH_TIME = "Startzeit";
    private static final String KEY_WISH_EXTRA = "Extra";
    
    //Tabellen-Zeilen
    private static final String[] GRANNI_COLUMNS = {KEY_GRANNIDATES_ID,KEY_GRANNIDATES_TIMESTAMP, KEY_GRANNIDATES_TITLE};
    private static final String[] SETTINGS_COLUMNS = {KEY_SETTINGS_ID,KEY_SETTINGS_TITLE, KEY_SETTINGS_VALUE};
    private static final String[] MODEL_COLUMNS = {KEY_MODEL_ID,KEY_MODEL_TITLE, KEY_MODEL_VALUE};
    private static final String[] WISH_COLUMNS = {KEY_WISH_ID,KEY_WISH_TITLE, KEY_WISH_TIME, KEY_WISH_EXTRA};
    
    //gibt die Instance des DatabaseBot zurück
    public static DatabaseBot getBot(Context context)
    {
    	if(selfBot == null)
    		selfBot = new DatabaseBot(context);
    	
    	return selfBot;
    }
    
    //privater Konstruktor, damit es nur eine Databasebotinstanze
    private DatabaseBot(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        
        this.context = context;
    }
 
    //Erstellen der Tabellen
    @Override
    public void onCreate(SQLiteDatabase db)
    {
        String CREATE_GRANNI_DATES_TABLE = "CREATE TABLE " + TABLE_GRANNI_NAME + " ( " + KEY_GRANNIDATES_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_GRANNIDATES_TIMESTAMP + " DATETIME NOT NULL, " + KEY_GRANNIDATES_TITLE + " VARCHAR(200))";
        String CREATE_SETTINGS_TABLE = "CREATE TABLE " + TABLE_SETTINGS_NAME + " ( " + KEY_SETTINGS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_SETTINGS_TITLE + " TEXT NOT NULL, " + KEY_SETTINGS_VALUE + " TEXT)";
        String CREATE_MODEL_TABLE = "CREATE TABLE " + TABLE_MODEL_NAME + " ( " + KEY_MODEL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_MODEL_TITLE + " TEXT NOT NULL, " + KEY_MODEL_VALUE + " TEXT)";
        String CREATE_WISH_TABLE = "CREATE TABLE " + TABLE_WISH_NAME + " ( " + KEY_WISH_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_WISH_TITLE + " TEXT NOT NULL, " + KEY_WISH_TIME + " LONG, " +KEY_WISH_EXTRA+" TEXT NOT NULL)";
        
        db.execSQL(CREATE_GRANNI_DATES_TABLE);
        db.execSQL(CREATE_SETTINGS_TABLE);
        db.execSQL(CREATE_MODEL_TABLE);
        db.execSQL(CREATE_WISH_TABLE);
    }
    
    //bei Versionswechsel der Datenbank, alle Tabellen löschen
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_GRANNI_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SETTINGS_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MODEL_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_WISH_NAME);
 
        this.onCreate(db);
    }
    
    //setze Geburtstag der Oma 
    // (bei uns derzeitig nicht in Verwendung)
    public void setBirthDay()
    {
    	ContentValues values = new ContentValues();
    	//mache Datumformat
    	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
    	Date date = new Date();
    	
    	values.put(KEY_GRANNIDATES_TIMESTAMP, dateFormat.format(date));
    	values.put(KEY_GRANNIDATES_TITLE, "Birthday");
    	
    	setData(TABLE_GRANNI_NAME, GRANNI_COLUMNS, KEY_GRANNIDATES_TIMESTAMP , values, KEY_GRANNIDATES_TITLE, "Birthday");
    }
    
    //setze Todestag der Oma 
    // (bei uns derzeitig nicht in Verwendung)
    public void setDayOfDeath()
    {
    	ContentValues values = new ContentValues();
    	//mache Datumformat
    	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
    	Date date = new Date();
    	
    	values.put(KEY_GRANNIDATES_TIMESTAMP, dateFormat.format(date));
    	values.put(KEY_GRANNIDATES_TITLE, "Deathday");
    	
    	setData(TABLE_GRANNI_NAME, GRANNI_COLUMNS, KEY_GRANNIDATES_TIMESTAMP , values, KEY_GRANNIDATES_TITLE, "Deathday");
    }
    
    public void setEmail(String email)
    {
    	ContentValues values = new ContentValues();
    	values.put(KEY_SETTINGS_TITLE, "Email");
    	values.put(KEY_SETTINGS_VALUE, email);
    	
    	setData(TABLE_SETTINGS_NAME, SETTINGS_COLUMNS, KEY_SETTINGS_VALUE, values, KEY_SETTINGS_TITLE, "Email");
    }
    
    public void setEmailService(boolean aktiv)
    {
    	ContentValues values = new ContentValues();
    	values.put(KEY_SETTINGS_TITLE, "EmailService");
    	values.put(KEY_SETTINGS_VALUE, ""+aktiv);

    	setData(TABLE_SETTINGS_NAME, SETTINGS_COLUMNS, KEY_SETTINGS_VALUE, values, KEY_SETTINGS_TITLE, "EmailService");
    }
    
    //Model speichern
    public void saveModelData()
    {
    	//Iteriere und speichere Lagerbestand, Kleidungsbestand und Geschir
    	for(int i=0; i<4; i++)
    	{
    		CustomMap tempMap = getMap(i);
    		Iterator<String> tempIterator = getIterator(i);
	    	
	    	while(tempIterator!= null && tempIterator.hasNext())
	    	{
		    	String tempTitel = tempIterator.next();
		    	
		    	ContentValues values = new ContentValues();
		    	values.put(KEY_MODEL_TITLE, tempTitel);
		    	values.put(KEY_MODEL_VALUE, tempMap.getCountOf(tempTitel));
		    	
		    	setData(TABLE_MODEL_NAME, MODEL_COLUMNS, KEY_MODEL_VALUE, values, KEY_MODEL_TITLE, tempTitel);
	    	}
    	}
    	
    	//Entferne alle Wünsche bevor man sie abspeichert -> Update der Wunsche
    	dropAllWishes();
    	
    	//Speichere derzeitige Wünsche
    	Iterator<AbstractWish> wunschIterator = Model.getInstance().getWishes().iterator();
    	AbstractWish tempWish = null;
    	while(wunschIterator.hasNext())
    	{
    		tempWish = wunschIterator.next();
    		
	    	ContentValues values = getContentValues(tempWish);
	    	setData(TABLE_WISH_NAME, WISH_COLUMNS, KEY_WISH_TIME, values, KEY_WISH_TITLE, tempWish.getType().name());
    	}
    	
    	//Aufwachzeiten speichern wenn es eine gibt
    	setWakeUpTime();
    }
    
    //Model wiederherstellen
    public void restoreModelData()
    {
    	//Stelle Lagerbestand, Kleidungsbestand und Geschir wieder her
    	for(int i=0; i<3; i++)
    	{
    		CustomMap tempMap = getMap(i);
    		Iterator<String> tempIterator = getIterator(i);
    		
	    	while(tempIterator.hasNext())
	    	{
		    	String tempTitel = tempIterator.next();
		    	
		    	String value = getData(TABLE_MODEL_NAME, MODEL_COLUMNS, KEY_MODEL_VALUE, KEY_MODEL_TITLE+" = ?", new String[] { tempTitel });
		    	if(value != null)
		    	{
			    	Log.i("Blob","Wiederhergestellt ("+tempTitel+") :"+value);
			    	restoreValue(i, tempTitel, value);
		    	}
	    	}
    	}
    	
    	//Stelle Wünsche wieder her und erstelle diese
    	SQLiteDatabase db = this.getWritableDatabase(); 
    	Cursor cursor = db.query(TABLE_WISH_NAME, WISH_COLUMNS, null, null, null, null, null, null);
    	if (cursor != null)
    	{
    	    if (cursor.moveToFirst())
    	    {
    	    	int i=0;
    	        do
    	        {
    	        	//Datenzusammenfassen
    	        	Log.i("Blob", "Cursorausgabe: "+cursor.getString(cursor.getColumnIndex(KEY_WISH_TITLE))+" Time: "+cursor.getString(cursor.getColumnIndex(KEY_WISH_TIME)));
    	        	WishType type = stringToWishType(cursor.getString(cursor.getColumnIndex(KEY_WISH_TITLE)));
    	        	Long startTime = cursor.getLong(cursor.getColumnIndex(KEY_WISH_TIME));
    	        	String extra = cursor.getString(cursor.getColumnIndex(KEY_WISH_EXTRA));
    	        	
    	        	//Wunsch generieren
    	        	WishFactory.createWish(type, startTime, extra);
    	        } while (cursor.moveToNext());
    	    }
    	}
    }
    
    //Gib für einen Index eine CustomMap
    //wird für die Iterator in restoreModelData und saveModelData verwendet
    private CustomMap getMap(int index)
    {
    	switch(index)
		{
			case 0:
				//Lagerbestand
				return Model.getInstance().getStock();
			case 1:
				//Kleidungsbestand
				return Model.getInstance().getCLothes();
			case 2:
				//Geschir
				return Model.getInstance().getDishes();
		}
    	
    	return null;
    }
    
    //Methode String zum Wunschtyp
    private WishType stringToWishType(String wishType)
    {
    	WishType returnWish = null;
    	
    	if(wishType.equals("WishToDrink"))
    		returnWish = WishType.WishToDrink;
    	else if(wishType.equals("WishToEat"))
    		returnWish = WishType.WishToEat;
    	else if(wishType.equals("WishToCleanApartment"))
    		returnWish = WishType.WishToCleanApartment;
    	else if(wishType.equals("WishToShop"))
    		returnWish = WishType.WishToShop;
    	else if(wishType.equals("WishToSleep"))
    		returnWish = WishType.WishToSleep;
    	else if(wishType.equals("WishToTakeMedicine"))
    		returnWish = WishType.WishToTakeMedicine;
    	else if(wishType.equals("WishToWashClothes"))
    		returnWish = WishType.WishToWashClothes;
    	else if(wishType.equals("WishToWashDishes"))
    		returnWish = WishType.WishToWashDishes;
    	
    	return returnWish;
    }
    
    //einen seperaten Wunsch abspeichern
    public void insertWish(AbstractWish wunsch)
    {
    	ContentValues values = getContentValues(wunsch);
    	setData(TABLE_WISH_NAME, WISH_COLUMNS, KEY_WISH_TIME, values, KEY_WISH_TITLE, wunsch.getType().name());
    }
    
    //erstellen des ContentValues für einen Wunsch
    private ContentValues getContentValues(AbstractWish wish)
    {
		ContentValues values = new ContentValues();

		values.put(KEY_WISH_TITLE, wish.getType().name());
		values.put(KEY_WISH_TIME, wish.getStartTime());

		switch (wish.getType()) {
		case WishToDrink:
			values.put(KEY_WISH_EXTRA, ((WishToDrink) wish).getDrink().getName());
			break;
		case WishToTakeMedicine:
			values.put(KEY_WISH_EXTRA, ((WishToTakeMedicine) wish).getMedicine());
			break;
		case WishToEat:
			values.put(KEY_WISH_EXTRA, ((WishToEat) wish).getFood().getName());
			break;
		case WishToSleep:
			values.put(KEY_WISH_EXTRA, ((WishToSleep) wish).getTimeToSleep());
			break;
		default:
			values.put(KEY_WISH_EXTRA, "");
			break;
		}		
		return values;
	}
    
    //prüfe ob es so einen Wunsch vom übergebenen Typen gibt
    public boolean existWishInDB(WishType wunsch)
    {
    	Log.i("Blob", wunsch.name()+": "+checkData(TABLE_WISH_NAME, WISH_COLUMNS, KEY_WISH_TITLE, KEY_WISH_TITLE+" = ?", new String[] {wunsch.name()}));
    	return checkData(TABLE_WISH_NAME, WISH_COLUMNS, KEY_WISH_TITLE, KEY_WISH_TITLE+" = ?", new String[] {wunsch.name()});
    }
    
    //Alle Wünsche entfernen
    private void dropAllWishes()
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	db.delete(TABLE_WISH_NAME, null, null);
    }
    
    //Zeitstempel für den Aufwachzeitpunkt
    public void setWakeUpTime()
    {
    	ContentValues values = new ContentValues();
    	values.put(KEY_SETTINGS_TITLE, "WakeUpTime");
    	values.put(KEY_SETTINGS_VALUE, ""+Model.getInstance().getWakeUpTime());

    	setData(TABLE_SETTINGS_NAME, SETTINGS_COLUMNS, KEY_SETTINGS_VALUE, values, KEY_SETTINGS_TITLE, "WakeUpTime");
    }
    
    //Getter des Aufwachzeitpunktes 
    public long getWakeUpTime()
    {
    	return getDataLong(TABLE_SETTINGS_NAME, SETTINGS_COLUMNS, KEY_SETTINGS_VALUE, KEY_SETTINGS_TITLE+" = ?", new String[] { "WakeUpTime" });
    }
    
    //entferne Aufwachzeitpunkt
    public void eraseWakeUpTime()
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	db.delete(TABLE_SETTINGS_NAME, KEY_SETTINGS_TITLE+" = 'WakeUpTime'",null);
    }
    
    //Iterator aus dem Model holen
    private Iterator<String> getIterator(int index)
    {
    	switch(index)
		{
			case 0:
				//Lagerbestand
				return Model.ingredientsNames().iterator();
			case 1:
				//Kleidungsbestand
				return Model.clothesNames().iterator();
			case 2:
				//Geschir
				return Model.dishesNames().iterator();
		}
    	
    	return null;
    }
    
    //Wert aus dem Model holen
    private void restoreValue(int index, String titel, String value)
    {
    	switch(index)
    	{
    		case 0:
    			//Lagerbestand
    			Model.getInstance().getStock().setCountOf(titel, Integer.parseInt(value));
    			break;
    		case 1:
    			//Kleidungsbestand
    			Model.getInstance().getCLothes().setCountOf(titel, Integer.parseInt(value));
    			break;
    		case 2:
    			//Geschir
    			Model.getInstance().getDishes().setCountOf(titel, Integer.parseInt(value));
    			break;
    	}
    }
    
    //Hole mir den Geburtstag der Granni
    // (derzietig nicht in Verwendung)
    public String getBirthDay()
    {	
    	return getData(TABLE_GRANNI_NAME, GRANNI_COLUMNS, KEY_GRANNIDATES_TIMESTAMP, KEY_GRANNIDATES_TITLE+" = ?", new String[] {"Birthday"});
    }
    
    //Hole mir den Todestag der Granni
    // (derzietig nicht in Verwendung)
    public String getDeadDay()
    {
    	return getData(TABLE_GRANNI_NAME, GRANNI_COLUMNS, KEY_GRANNIDATES_TIMESTAMP, KEY_GRANNIDATES_TITLE+" = ?", new String[] {"Deathday"});
    }
    
    //hole mir die gespeicherte Emailadresse
    public String getEmail()
    {	
    	String email = getData(TABLE_SETTINGS_NAME, SETTINGS_COLUMNS, KEY_SETTINGS_VALUE, KEY_SETTINGS_TITLE+" = ?", new String[] {"Email"});
    	
    	if(email == null)
    		email = "";
    	
    	return email;
    }
    
    //hole mir den Wert ob Emailservice aktiv -> true/false
    public boolean getEmailService()
    {	
    	String emailService = getData(TABLE_SETTINGS_NAME, SETTINGS_COLUMNS, KEY_SETTINGS_VALUE, KEY_SETTINGS_TITLE+" = ?", new String[] {"EmailService"});
    	
    	if(emailService == null || emailService.equals("false"))
    		return false;
    	
    	return true;
    }
    
    //hauptMethode zum Überschreiben in der Datenbank
    //wenn keine Datensetze vorhanden, wird insert aufgerufen, sonst update
    private void setData(String tableName, String[] allColums, String targetColum, ContentValues values, String catchString, String where)
    {
    	if(checkData(tableName, allColums, targetColum, catchString+" = ?", new String[] {where}))
    	{
    		Log.i("Blob", "DB: update "+tableName+" -> "+values.toString());
    		updateData(tableName, values, catchString, where);
    	}
    	else
    	{
    		Log.i("Blob", "DB: insert "+tableName+" -> "+values.toString());
    		insertData(tableName, values);
    	}
    }
    
    //hauptMethode zum Schreiben in die Datenbank
    private void updateData(String tableName, ContentValues values, String whereCatch, String where)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	db.update(tableName, values, whereCatch+" = ?", new String[] {where});
    }
    
    //hauptMethode zum Schreiben in die Datenbank
    private void insertData(String tableName, ContentValues values)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	db.insert(tableName, null, values);
    }
    
    //Hauptmethode zum Auslesen aus der Datenbank
    private String getData(String tableName, String[] allColums, String targetColum, String where, String[] whereArgs)
    {
    	String returnString = null;
    	SQLiteDatabase db = this.getWritableDatabase(); 
    	
    	//Durchlaufe alle Rows und speichere den letzten gefunden Wert
    	Cursor cursor = db.query(tableName, allColums, where, whereArgs, null, null, null, null);
    	if (cursor != null)
    	{
    	    if (cursor.moveToFirst())
    	    {
    	        do
    	        {
    	        	returnString = cursor.getString(cursor.getColumnIndex(targetColum));
    	        } while (cursor.moveToNext());
    	    }
    	}
    	return returnString;
    }
    
    private long getDataLong(String tableName, String[] allColums, String targetColum, String where, String[] whereArgs)
    {
    	long returnString = 0;
    	SQLiteDatabase db = this.getWritableDatabase(); 
    	
    	//Durchlaufe alle Rows und speichere den letzten gefunden Wert
    	Cursor cursor = db.query(tableName, allColums, where, whereArgs, null, null, null, null);
    	if (cursor != null)
    	{
    	    if (cursor.moveToFirst())
    	    {
    	        do
    	        {
    	        	returnString = cursor.getLong(cursor.getColumnIndex(targetColum));
    	        } while (cursor.moveToNext());
    	    }
    	}
    	return returnString;
    }
    
    //Check ob ein Datensatz schon existiert
    private boolean checkData(String tableName, String[] allColums, String targetColum, String where, String[] whereArgs)
    {
    	SQLiteDatabase db = this.getWritableDatabase(); 
    	
    	//Durchlaufe alle Rows, wenn es so einen Datensatz gibt - gib TRUE zurück
    	Cursor cursor = db.query(tableName, allColums, where, whereArgs, null, null, null, null);
    	if (cursor != null)
    	{
    		if(cursor.moveToFirst())
    		{
    			return true;
    		}
    	}
    	
    	return false;
    }
    
    //Lösche den Inhalt der genutzten Datensätze
    public void resetDatabase()
    {
    	Log.i("SWIPE", "In resetDatabase!!");
    	SQLiteDatabase db = this.getWritableDatabase();
    	
    	db.delete(TABLE_GRANNI_NAME, null, null);
    	db.delete(TABLE_SETTINGS_NAME, null, null);
    	db.delete(TABLE_MODEL_NAME, null, null);
    	db.delete(TABLE_WISH_NAME, null, null);
    }
}